/* Modified version of Holden & Jacobsen.  Calculates effective spreads and Kyle's lambda. */

libname project '/scratch/institution/2013';
options errors=50;

/* STEP 1: RETRIEVE DAILY TRADE AND QUOTE (DTAQ) FILES FROM WRDS */

    libname nbbo '/wrds/nyse/sasdata/taqms/nbbo';
    libname cq '/wrds/nyse/sasdata/taqms/cq';
    libname ct '/wrds/nyse/sasdata/taqms/ct';

	/*    option msglevel=i mprint source; */

	%let thisyear=2013;

	%macro setvar(tickerObsNo=);
   		%let dsid=%sysfunc(open(ticker_list,i));
   		%syscall set(dsid); 
   		%let rc=%sysfunc(fetchobs(&dsid,&tickerObsNo));
   		%let rc=%sysfunc(close(&dsid));
	%mend setvar;

	%macro mainprogram;

		data ticker_list;
		infile '/scratch/institution/2013/ticker_list.txt';
		input tickersym $;
		run;

	%let dsid=%sysfunc(open(ticker_list,i));
	%let num=%sysfunc(attrn(&dsid,nlobs));
	%let rc=%sysfunc(close(&dsid));
	%do n=1 %to &num; 
		%global tickersym;
		%setvar(tickerObsNo=&n);
		%put _global_;
    	%readdata(ticker1=&tickersym);
     %end;
    %mend mainprogram; 

	%macro readdata(ticker1=);

	
    /* Retrieve NBBO data from files in YYYYMMDD format */

    data project.DailyNBBO;
		set 
		%do i=1 %to 12;
		%let prefix1=0;
		%if &&i>9 %then %let prefix1=;
			
			%do j=1 %to 31;
			%let prefix2=0;
			%if &&j>9 %then %let prefix2=;
			% let ex=%sysfunc(exist(nbbo.nbbom_2013&&prefix1.&&i&&prefix2.&&j,data));
			%if &&ex=1 %then %do;
			nbbo.nbbom_2013&&prefix1.&&i&&prefix2.&&j /*(where=(sym_root="&k" and time_m between "09:30:00.000000"t and "16:00:00.000000"t)) */
/*			nbbo.nbbom_2013&&prefix1.&&i&&prefix2.&&j (where=(sym_root="&k" and time_m between "16:00:00.000000"t and "16:00:30.000000"t and TR_SCOND='M')) */
			%end;
			%end;
		%end;
		;
        where sym_root EQ "&ticker1" and

        /* Quotes are retrieved prior to market open time to ensure NBBO 
		   Quotes are available for beginning of the day trades */
        (("9:00:00.000000"t) <= time_m <= ("16:00:00.000000"t));
        format date date9.;
        format time_m part_time trf_time TIME20.6;
    run;


	/* Retrieve Quote data from files in YYYYMMDD format */

 
   	data project.DailyQuote;
		set 
		%do i=1 %to 12;
		%let prefix1=0;
		%if &&i>9 %then %let prefix1=;
			
			%do j=1 %to 31;
			%let prefix2=0;
			%if &&j>9 %then %let prefix2=;
			% let ex=%sysfunc(exist(cq.cqm_2013&&prefix1.&&i&&prefix2.&&j,data));
			%if &&ex=1 %then %do;
			cq.cqm_2013&&prefix1.&&i&&prefix2.&&j /*(where=(sym_root="&k" and time_m between "09:30:00.000000"t and "16:00:00.000000"t)) */
/*			cq.cqm_2013&&prefix1.&&i&&prefix2.&&j (where=(sym_root="&k" and time_m between "16:00:00.000000"t and "16:00:30.000000"t and TR_SCOND='M')) */
			%end;
			%end;
		%end;
		;


        /* Enter company tickers */
        where sym_root EQ "&ticker1" and

        /* Quotes are retrieved prior to market open time to ensure NBBO 
		   Quotes are available for beginning of the day trades*/
		(("9:00:00.000000"t) <= time_m <= ("16:00:00.000000"t));
        format date date9.;
        format time_m part_time trf_time TIME20.6;
    run;

	
    data project.DailyTrade (drop=G127 COND TSEQ);

        /* Enter the same MTAQ dates as above in YYYYMMDD format for 
	       Consolidated Trade files ("ct")*/
        set 
		%do i=1 %to 12;
		%let prefix1=0;
		%if &&i>9 %then %let prefix1=;
			
			%do j=1 %to 31;
			%let prefix2=0;
			%if &&j>9 %then %let prefix2=;
			% let ex=%sysfunc(exist(ct.ctm_2013&&prefix1.&&i&&prefix2.&&j,data));
			%if &&ex=1 %then %do;
			ct.ctm_2013&&prefix1.&&i&&prefix2.&&j /*(where=(sym_root="&k" and time_m between "09:30:00.000000"t and "16:00:00.000000"t)) */
/*			ct.ctm_2013&&prefix1.&&i&&prefix2.&&j (where=(sym_root="&k" and time_m between "16:00:00.000000"t and "16:00:30.000000"t and TR_SCOND='M')) */
			%end;
			%end;
		%end;
		;

        /* Enter same company tickers as above */
        where sym_root EQ "&ticker1" and 

        /* Retrieve trades during normal market hours */
		(("9:30:00.000000"t) <= time_m <= ("16:00:00.000000"t));
        type='T';
        format date date9.;
		format time_m part_time trf_time TIME20.6;
    run;



/* STEP 2: CLEAN THE DTAQ NBBO FILE */ 

data NBBO2;
    set project.DailyNBBO;

    /* Quote Condition must be normal (i.e., A,B,H,O,R,W) */
    if Qu_Cond not in ('A','B','H','O','R','W') then delete;

	/* If canceled then delete */
    if Qu_Cancel='B' then delete;

	/* if both ask and bid are set to 0 or . then delete */
    if Best_Ask le 0 and Best_Bid le 0 then delete;
    if Best_Asksiz le 0 and Best_Bidsiz le 0 then delete;
    if Best_Ask = . and Best_Bid = . then delete;
    if Best_Asksiz = . and Best_Bidsiz = . then delete;

	/* Create spread and midpoint */
    Spread=Best_Ask-Best_Bid;
    Midpoint=(Best_Ask+Best_Bid)/2;

	/* If size/price = 0 or . then price/size is set to . */
    if Best_Ask le 0 then do;
        Best_Ask=.;
        Best_Asksiz=.;
    end;
    if Best_Ask=. then Best_Asksiz=.;
    if Best_Asksiz le 0 then do;
        Best_Ask=.;
        Best_Asksiz=.;
    end;
    if Best_Asksiz=. then Best_Ask=.;
    if Best_Bid le 0 then do;
        Best_Bid=.;
        Best_Bidsiz=.;
    end;
    if Best_Bid=. then Best_Bidsiz=.;
    if Best_Bidsiz le 0 then do;
        Best_Bid=.;
        Best_Bidsiz=.;
    end;
    if Best_Bidsiz=. then Best_Bid=.;

	/*	Bid/Ask size are in round lots, replace with new shares variable*/
	Best_BidSizeShares = Best_BidSiz * 100;
	Best_AskSizeShares = Best_AskSiz * 100;
run;

/* STEP 3: GET PREVIOUS MIDPOINT */

proc sort 
    data=NBBO2 (drop = Best_BidSiz Best_AskSiz);
    by sym_root date;
run; 

data NBBO2;
    set NBBO2;
    by sym_root date;
    lmid=lag(Midpoint);
    if first.sym_root or first.date then lmid=.;
    lm25=lmid-2.5;
    lp25=lmid+2.5;
run;

/* If the quoted spread is greater than $5.00 and the bid (ask) price is less
   (greater) than the previous midpoint - $2.50 (previous midpoint + $2.50), 
   then the bid (ask) is not considered. */

data NBBO2;
    set NBBO2;
    if Spread gt 5 and Best_Bid lt lm25 then do;
        Best_Bid=.;
        Best_BidSizeShares=.;
    end;
    if Spread gt 5 and Best_Ask gt lp25 then do;
        Best_Ask=.;
        Best_AskSizeShares=.;
    end;
	keep date time_m sym_root Best_Bidex Best_Bid Best_BidSizeShares Best_Askex 
         Best_Ask Best_AskSizeShares Qu_SeqNum;
run;

/* STEP 4: OUTPUT NEW NBBO RECORDS - IDENTIFY CHANGES IN NBBO RECORDS 
   (CHANGES IN PRICE AND/OR DEPTH) */

data NBBO2;
    set NBBO2;
    if sym_root ne lag(sym_root) 
       or date ne lag(date) 
       or Best_Ask ne lag(Best_Ask) 
       or Best_Bid ne lag(Best_Bid) 
       or Best_AskSizeShares ne lag(Best_AskSizeShares) 
       or Best_BidSizeShares ne lag(Best_BidSizeShares); 
run;

/* STEP 5: CLEAN DTAQ QUOTES DATA */

data quoteAB;
    set project.DailyQuote;

    /* Create spread and midpoint*/;
    Spread=Ask-Bid;

	/* Delete if abnormal quote conditions */
    if Qu_Cond not in ('A','B','H','O','R','W')then delete; 

	/* Delete if abnormal crossed markets */
    if Bid>Ask then delete;

	/* Delete abnormal spreads*/
    if Spread>5 then delete;

	/* Delete withdrawn Quotes. This is 
	   when an exchange temporarily has no quote, as indicated by quotes 
	   with price or depth fields containing values less than or equal to 0 
	   or equal to '.'. See discussion in Holden and Jacobsen (2014), 
	   page 11. */
    if Ask le 0 or Ask =. then delete;
    if Asksiz le 0 or Asksiz =. then delete;
    if Bid le 0 or Bid =. then delete;
    if Bidsiz le 0 or Bidsiz =. then delete;
	drop Sym_Suffix Bidex Askex Qu_Cancel Qu_Source RPI SSR LULD_BBO_CQS 
         LULD_BBO_UTP FINRA_ADF_MPID SIP_Message_ID Part_Time RRN TRF_Time 
         Spread NATL_BBO_LULD;
run;

/* STEP 6: CLEAN DAILY TRADES DATA - DELETE ABNORMAL TRADES */

data trade2;
    set project.DailyTrade;
    where Tr_Corr eq '00' and price gt 0;
	drop Tr_Corr Tr_Source TR_RF Part_Time RRN TRF_Time Sym_Suffix Tr_SCond 
         Tr_StopInd;
run;

/* STEP 7: THE NBBO FILE IS INCOMPLETE BY ITSELF (IF A SINGLE EXCHANGE 
   HAS THE BEST BID AND OFFER, THE QUOTE IS INCLUDED IN THE QUOTES FILE, BUT 
   NOT THE NBBO FILE). TO CREATE THE COMPLETE OFFICIAL NBBO, WE NEED TO 
   MERGE WITH THE QUOTES FILE (SEE FOOTNOTE 6 AND 24 IN OUR PAPER) */

data quoteAB2 (rename=(Ask=Best_Ask Bid=Best_Bid));
    set quoteAB;
    where NatBBO_Ind='1' or NASDBBO_Ind='4';
    keep date time_m sym_root Qu_SeqNum Bid Best_BidSizeShares Ask Best_AskSizeShares;

	/*	Bid/Ask size are in round lots, replace with new shares variable
	and rename Best_BidSizeShares and Best_AskSizeShares*/
	Best_BidSizeShares = Bidsiz * 100;
	Best_AskSizeShares = Asksiz * 100;
run;

proc sort data=NBBO2;
    by sym_root date Qu_SeqNum;
run;

proc sort data=quoteAB2;
    by sym_root date Qu_SeqNum;
run;

data project.OfficialCompleteNBBO (drop=Best_Askex Best_Bidex);
    set NBBO2 quoteAB2;
    by sym_root date Qu_SeqNum;
run;

/* If the NBBO Contains two quotes in the exact same microseond, assume 
   last quotes (based on sequence number) is active one */
proc sort data=project.OfficialCompleteNBBO;
    by sym_root date time_m descending Qu_SeqNum;
run;

proc sort data=project.OfficialCompleteNBBO nodupkey;
    by sym_root date time_m;
run;

/* STEP 8: INTERLEAVE TRADES WITH NBBO QUOTES. DTAQ TRADES AT MICROuiucOND 
   TMMMMMM ARE MATCHED WITH THE DTAQ NBBO QUOTES STILL IN FORCE AT THE 
   MICROuiucOND TMMMMM(M-1) */;

data project.OfficialCompleteNBBO;
    set project.OfficialCompleteNBBO;type='Q';
    time_m=time_m+.000001;
	drop Qu_SeqNum;
run;

proc sort data=project.OfficialCompleteNBBO;
    by sym_root date time_m;
run;

proc sort data=trade2;
    by sym_root date time_m Tr_SeqNum;
run;

data project.TradesPriorNBBOandQuotes;
    set project.OfficialCompleteNBBO trade2;
    by sym_root date time_m type;
run;

data project.TradesPriorNBBOandQuotes (drop=Best_Ask Best_Bid Best_AskSizeShares
    Best_BidSizeShares);
    set project.TradesPriorNBBOandQuotes;
    by sym_root date;
    retain QTime NBO NBB NBOqty NBBqty;
    if first.sym_root or first.date and type='T' then do;
		QTime=.;
        NBO=.;
        NBB=.;
        NBOqty=.;
        NBBqty=.;
    end;
    if type='Q' then Qtime=time_m;
        else Qtime=Qtime;
    if type='Q' then NBO=Best_Ask;
        else NBO=NBO;
    if type='Q' then NBB=Best_Bid;
        else NBB=NBB;
    if type='Q' then NBOqty=Best_AskSizeShares;
        else NBOqty=NBOqty;
    if type='Q' then NBBqty=Best_BidSizeShares;
        else NBBqty=NBBqty;
	format Qtime TIME20.6;
run;

/* STEP 9: CLASSIFY TRADES AS "BUYS" OR "SELLS" USING THREE CONVENTIONS:
   LR = LEE AND READY (1991), EMO = ELLIS, MICHAELY, AND O'HARA (2000)
   AND CLNV = CHAKRABARTY, LI, NGUYEN, AND VAN NESS (2006); DETERMINE NBBO 
   MIDPOINT AND LOCKED AND CROSSED NBBOs */

data project.&ticker1._BuySellIndicators;
    set project.TradesPriorNBBOandQuotes;
    where type='T';
    midpoint=(NBO+NBB)/2;
    if NBO=NBB then lock=1;else lock=0;
    if NBO<NBB then cross=1;else cross=0;
run;

/* Determine Whether Trade Price is Higher or Lower than Previous Trade 
   Price, or "Trade Direction" */
data project.&ticker1._BuySellIndicators;
    set project.&ticker1._BuySellIndicators;
    by sym_root date;
	retain direction2;
    direction=dif(price);
    if first.sym_root or first.date then direction=.;
    if direction ne 0 then direction2=direction; 
    else direction2=direction2;
	drop direction;
run;

/* First Classification Step: Classify Trades Using Tick Test */
data project.&ticker1._BuySellIndicators;
    set project.&ticker1._BuySellIndicators;
    if direction2>0 then BuySellLR=1;
    if direction2<0 then BuySellLR=-1;
    if direction2=. then BuySellLR=.;
    if direction2>0 then BuySellEMO=1;
    if direction2<0 then BuySellEMO=-1;
    if direction2=. then BuySellEMO=.;
    if direction2>0 then BuySellCLNV=1;
    if direction2<0 then BuySellCLNV=-1;
    if direction2=. then BuySellCLNV=.;
run;

/* uiucond Classification Step: Update Trade Classification When 
   Conditions are Met as Specified by LR, EMO, and CLNV */
data project.&ticker1._BuySellIndicators;
    set project.&ticker1._BuySellIndicators;
    if lock=0 and cross=0 and price gt midpoint then BuySellLR=1;
    if lock=0 and cross=0 and price lt midpoint then BuySellLR=-1;
    if lock=0 and cross=0 and price=NBO then BuySellEMO=1;
    if lock=0 and cross=0 and price=NBB then BuySellEMO=-1;
    ofr30=NBO-.3*(NBO-NBB);
    bid30=NBB+.3*(NBO-NBB);
    if lock=0 and cross=0 and price le NBO and price ge ofr30
        then BuySellCLNV=1;
    if lock=0 and cross=0 and price le bid30 and price ge NBB 
        then BuySellCLNV=-1;
run;

/* STEP 10: CALCULATE QUOTED SRPEADS AND DEPTHS */

/* Use Quotes During Normal Market Hours */
data QSpread1;
    set project.OfficialCompleteNBBO;
    if time_m lt ("9:30:00.000000"t) then delete;
run;

/* Determine Time Each Quote is In Force - If Last Quote of Day, then Quote
   is Inforce Until 4:00 pm */

proc sort data=QSpread1;
    by sym_root date descending time_m;
run;

data QSpread1;
    set QSpread1;
    by sym_root date;
    inforce=abs(dif(time_m));
	if first.sym_root or first.date 
    then inforce=max(("16:00:00.000000"t-time_m),0);
	midpoint=(Best_Ask+Best_Bid)/2;
run;

proc sort data=QSpread1;
    by sym_root date time_m;
run;

data QSpread2;
    set QSpread1;
/* Delete Locked and Crossed Quotes */
    if Best_Ask=Best_Bid or Best_Ask<Best_Bid then delete;
/* Multiply Dollar Quoted Spread, Percent Quoted Spread, Best Dollar 
   Depth, and Best Share Depth by Time Inforce */
    wQuotedSpread_Dollar=(Best_Ask-Best_Bid)*inforce;
    wQuotedSpread_Percent=(log(Best_Ask)-log(Best_Bid))*inforce;
    wBestOfrDepth_Dollar=Best_Ask*Best_AskSizeShares*inforce;
    wBestBidDepth_Dollar=Best_Bid*Best_BidSizeShares*inforce;
    wBestOfrDepth_Share=Best_AskSizeShares*inforce;
    wBestBidDepth_Share=Best_BidSizeShares*inforce;
run;

/* Find Average Across Firm-Day */
proc sql;
    create table project.QuotedSpreadsandDepths 
    as select sym_root,date,
    sum(inforce) as sumtime,
    sum(wQuotedSpread_Dollar) as swQuotedSpread_Dollar,
    sum(wQuotedSpread_Percent) as swQuotedSpread_Percent,
    sum(wBestOfrDepth_Dollar) as swBestOfrDepth_Dollar,
    sum(wBestBidDepth_Dollar) as swBestBidDepth_Dollar,
    sum(wBestOfrDepth_Share) as swBestOfrDepth_Share,
    sum(wBestBidDepth_Share) as swBestBidDepth_Share 
    from QSpread2 
    group by sym_root,date 
    order by sym_root,date;
quit;

/* Calculate Time-Weighted Dollar Quoted Spread, Percent Quoted Spread, 
   Best Dollar Depth, and Best Share Depth */
data project.QuotedSpreadsandDepths;
    set project.QuotedSpreadsandDepths;
    QuotedSpread_Dollar=swQuotedSpread_Dollar/sumtime;
    QuotedSpread_Percent=swQuotedSpread_Percent/sumtime;
    BestOfrDepth_Dollar=swBestOfrDepth_Dollar/sumtime;
    BestBidDepth_Dollar=swBestBidDepth_Dollar/sumtime;
    BestOfrDepth_Share=swBestOfrDepth_Share/sumtime;
    BestBidDepth_Share=swBestBidDepth_Share/sumtime;
	drop swQuotedSpread_Dollar swQuotedSpread_Percent 
         swBestOfrDepth_Dollar swBestBidDepth_Dollar
         swBestOfrDepth_Share swBestBidDepth_Share;
run;

proc append base=project.merged_quotes_&thisyear. data=project.QuotedSpreadsandDepths;
run;

/* STEP 11: CALCULATE EFFECTIVE SPREADS; AGGREGATE BASED ON 3 CONVENTIONS:
   Ave = SIMPLE AVERAGE, DW = DOLLAR-WEIGHTED, SW = SHARE-WEIGHTED */
data project.&ticker1._BuySellIndicators;
    set project.&ticker1._BuySellIndicators;
    wEffectiveSpread_Dollar=(abs(price-midpoint))*2;
    wEffectiveSpread_Percent=abs(log(price)-log(midpoint))*2;
    dollar=price*size;
    wEffectiveSpread_Dollar_DW=wEffectiveSpread_Dollar*dollar;
    wEffectiveSpread_Dollar_SW=wEffectiveSpread_Dollar*size;
    wEffectiveSpread_Percent_DW=wEffectiveSpread_Percent*dollar;
    wEffectiveSpread_Percent_SW=wEffectiveSpread_Percent*size;
run;

/* Delete Trades Associated with Locked or Crossed Best Bids or Best 
   Offers */
data TSpread2;
    set project.&ticker1._BuySellIndicators;
    if lock=1 or cross=1 then delete;
run;

/* Find average across firm-day */
proc sql;
    create table project.&ticker1._EffectiveSpreads 
    as select sym_root,date,
    sum(dollar) as sumdollar,
    sum(size) as sumsize,
    mean(wEffectiveSpread_Dollar) as EffectiveSpread_Dollar_Ave,
    mean(wEffectiveSpread_Percent) as EffectiveSpread_Percent_Ave,
    sum(wEffectiveSpread_Dollar_DW) as waEffectiveSpread_Dollar_DW,
    sum(wEffectiveSpread_Dollar_SW) as waEffectiveSpread_Dollar_SW,
    sum(wEffectiveSpread_Percent_DW) as waEffectiveSpread_Percent_DW,
    sum(wEffectiveSpread_Percent_SW) as waEffectiveSpread_Percent_SW 
    from TSpread2 
    group by sym_root,date 
    order by sym_root,date;
quit;

/* Calculate Dollar-Weighted (DW) and Share-Weighted (SW) Dollar Effective 
   Spreads and Percent Effective Spreads */
data project.&ticker1._EffectiveSpreads;
    set project.&ticker1._EffectiveSpreads;
    EffectiveSpread_Dollar_DW=waEffectiveSpread_Dollar_DW/sumdollar;
    EffectiveSpread_Dollar_SW=waEffectiveSpread_Dollar_SW/sumsize;
    EffectiveSpread_Percent_DW=waEffectiveSpread_Percent_DW/sumdollar;
    EffectiveSpread_Percent_SW=waEffectiveSpread_Percent_SW/sumsize;
	drop waEffectiveSpread_Dollar_DW waEffectiveSpread_Dollar_SW
         waEffectiveSpread_Percent_DW waEffectiveSpread_Percent_SW;
run;



proc append base=project.merged_effective_&thisyear. data=project.&ticker1._EffectiveSpreads;
run;

/* Calculation of Kyle's Lambda */


%let firsttime = HMS(09,30,00);

data project.&ticker1._fiveminutequotes (drop= dummy);
	set project.OfficialCompleteNBBO;
	by sym_root date time_m;
	array dummy{79} dummy1-dummy79;
	retain lasttime lastbestbid lastbestofr lastmidpoint dummy;
	if first.date then 
		do;
		do j = 1 to 79;
			dummy(j) = 0;
		end;
		end;
	do j = 1 to 79;
		cut_off_time = &firsttime + (j-1)*300;
		if ((time_m GT cut_off_time) AND (dummy(j) EQ 0)) then 
			do;
				dummy(j) = 1;
				output; 
			end;
	end;
	lasttime = time_m;
	lastbestbid = Best_Bid;
	lastbestofr = Best_Ask;
	lastmidpoint = (Best_Bid + Best_Ask) / 2;
run;

data project.&ticker1._fiveminutereturns;
	set project.&ticker1._fiveminutequotes;
    by sym_root date time_m;
	if first.date then
		do;
			sreturn = 0;
		end;
	else
		do;
			sreturn = (log(lastmidpoint) - log(lag(lastmidpoint)))*100;
			if sreturn GT 5 then sreturn = 5;
			if sreturn LT -5 then sreturn = -5;
			output;
		end;
run;

data project.&ticker1._vol_buckets (drop= endtime dummy totsum);
	set project.&ticker1._BuySellIndicators;
	by date time_m;
	array endtime{79} endtime1-endtime79;
	array totsum{79} totsum1-totsum79;
	array dummy{79} dummy1-dummy79;
	retain totsum endtime dummy;
	if first.date then
		do;
		do j = 1 to 79;
			totsum(j) = 0;
			dummy(j) = 0;
			endtime(j) = &firsttime + (j-1)*300;
		end;
		end;
	do j = 2 to 79;
		if(time_m GT endtime(j-1) AND time_m LE endtime(j)) then
			do;
				totsum(j) = SUM(totsum(j),buysellLR*sqrt(dollar));
			end;
		if(time_m GT endtime(j) and dummy(j) EQ 0) then
			do;
				dummy(j) = 1;
				cut_off_time = endtime(j);
				totalsum = totsum(j);
				output;
			end;
	end;
run;

data project.&ticker1._combined (keep=sym_root date cut_off_time totalsum sreturn);
	merge project.&ticker1._fiveminutereturns project.&ticker1._vol_buckets;
	by date cut_off_time;
run;

/* split data into 12 months - run reg on each month below */


proc reg data=project.&ticker1._combined outest =project.&ticker1._est1;
	model sreturn  = totalsum / noint;
	where month(date) EQ 1 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est2;
	model sreturn  = totalsum / noint;
	where month(date) EQ 2 and cut_off_time GT 34500;
run;


proc reg data=project.&ticker1._combined outest =project.&ticker1._est3;
	model sreturn  = totalsum / noint;
	where month(date) EQ 3 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est4;
	model sreturn  = totalsum / noint;
	where month(date) EQ 4 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est5;
	model sreturn  = totalsum / noint;
	where month(date) EQ 5 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est6;
	model sreturn  = totalsum / noint;
	where month(date) EQ 6 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est7;
	model sreturn  = totalsum / noint;
	where month(date) EQ 7 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est8;
	model sreturn  = totalsum / noint;
	where month(date) EQ 8 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est9;
	model sreturn  = totalsum / noint;
	where month(date) EQ 9 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est10;
	model sreturn  = totalsum / noint;
	where month(date) EQ 10 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est11;
	model sreturn  = totalsum / noint;
	where month(date) EQ 11 and cut_off_time GT 34500;
run;

proc reg data=project.&ticker1._combined outest =project.&ticker1._est12;
	model sreturn  = totalsum / noint;
	where month(date) EQ 12 and cut_off_time GT 34500;
run;



data project.&ticker1._est1b;
	set project.&ticker1._est1;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est2b;
	set project.&ticker1._est2;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;


data project.&ticker1._est3b;
	set project.&ticker1._est3;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;


data project.&ticker1._est4b;
	set project.&ticker1._est4;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est5b;
	set project.&ticker1._est5;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est6b;
	set project.&ticker1._est6;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est7b;
	set project.&ticker1._est7;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est8b;
	set project.&ticker1._est8;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est9b;
	set project.&ticker1._est9;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est10b;
	set project.&ticker1._est10;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est11b;
	set project.&ticker1._est11;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;

data project.&ticker1._est12b;
	set project.&ticker1._est12;
	ticker = "AAAAAAA";
	ticker = "&ticker1";
run;



proc append base=project.lambda1_&thisyear. data=project.&ticker1._est1b force;
run;
proc append base=project.lambda2_&thisyear. data=project.&ticker1._est2b force;
run;
proc append base=project.lambda3_&thisyear. data=project.&ticker1._est3b force;
run;
proc append base=project.lambda4_&thisyear. data=project.&ticker1._est4b force;
run;
proc append base=project.lambda5_&thisyear. data=project.&ticker1._est5b force;
run;
proc append base=project.lambda6_&thisyear. data=project.&ticker1._est6b force;
run;
proc append base=project.lambda7_&thisyear. data=project.&ticker1._est7b force;
run;
proc append base=project.lambda8_&thisyear. data=project.&ticker1._est8b force;
run;
proc append base=project.lambda9_&thisyear. data=project.&ticker1._est9b force;
run;
proc append base=project.lambda10_&thisyear. data=project.&ticker1._est10b force;
run;
proc append base=project.lambda11_&thisyear. data=project.&ticker1._est11b force;
run;
proc append base=project.lambda12_&thisyear. data=project.&ticker1._est12b force;
run;



	proc datasets library=project nolist;
		delete &ticker1._est1;
		delete &ticker1._est2;
		delete &ticker1._est3;
		delete &ticker1._est4;
		delete &ticker1._est5;
		delete &ticker1._est6;
		delete &ticker1._est7;
		delete &ticker1._est8;
		delete &ticker1._est9;
		delete &ticker1._est10;
		delete &ticker1._est11;
		delete &ticker1._est12;
		delete &ticker1._est1b;
		delete &ticker1._est2b;
		delete &ticker1._est3b;
		delete &ticker1._est4b;
		delete &ticker1._est5b;
		delete &ticker1._est6b;
		delete &ticker1._est7b;
		delete &ticker1._est8b;
		delete &ticker1._est9b;
		delete &ticker1._est10b;
		delete &ticker1._est11b;
		delete &ticker1._est12b;
		delete &ticker1._buysellindicators;
		delete &ticker1._effectivespreads;
		delete &ticker1._fiveminutequotes; 
		delete &ticker1._fiveminutereturns;
		delete &ticker1._vol_buckets;
		delete &ticker1._combined;

run; 
%mend readdata;
%mainprogram;
